SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



ALTER  trigger [tb_comcheckacceptraw_bd] on dbo.tb_comcheckacceptraw instead of delete 
as
  declare @obid integer,
          @obno integer,
          @obdtlno integer,
          @oproductid integer,
          @obkind integer,
          @obstate integer,
          @orelationbid integer,
          @orelationbno integer,
          @orelationbdtlno integer,
          @orelationbtype integer,
          @oquantity decimal(18,4),
          @orelationcount integer,
          @bdate datetime,
          @subproductid integer 
begin
  set nocount on
  declare tb_comcheckacceptraw_cur cursor local forward_only static optimistic for
  select o.bid,o.bno,o.bdtlno,o.productid,o.relationbtype,o.relationbid,o.relationbno,o.relationbdtlno,
    o.relationcount, o.quantity,isnull(s.subproductid,0),m.bdate,m.bstate,m.bkind 
    from deleted o 
    left join tb_commissionrawsub s on s.bid = o.relationbid and s.bno=o.relationbno and s.bdtlno=o.relationbdtlno and s.subproductid=o.productid
    left join tb_comcheckaccept m on m.bid = o.bid
  open tb_comcheckacceptraw_cur
  fetch next from tb_comcheckacceptraw_cur into
  @obid,@obno,@obdtlno,@oproductid,@orelationbtype,@orelationbid,@orelationbno,@orelationbdtlno,
  @orelationcount,@oquantity,@subproductid,@bdate,@obstate,@obkind
  while (@@fetch_status = 0)
  begin
    if (@orelationbtype = 1110 )
    begin
      if (@subproductid = 0) 
      begin
        update tb_commissionraw set wastedqty=wastedqty-@oquantity
          where bid=@orelationbid and bno=@orelationbno and bdtlno=@orelationbdtlno
      end
      else
      begin
        update tb_commissionrawsub set wastedqty=wastedqty-@oquantity
          where bid=@orelationbid and bno=@orelationbno and bdtlno=@orelationbdtlno and subproductid=@oproductid
      end
    end

    delete from tb_comcheckacceptraw where bid=@obid and bno=@obno and bdtlno=@obdtlno

    fetch next from tb_comcheckacceptraw_cur into
    @obid,@obno,@obdtlno,@oproductid,@orelationbtype,@orelationbid,@orelationbno,@orelationbdtlno,
    @orelationcount,@oquantity,@subproductid,@bdate,@obstate,@obkind
  end
  close tb_comcheckacceptraw_cur
  deallocate  tb_comcheckacceptraw_cur
end










GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

